/**
 * Copyright (c) 2013-Now http://jeesite.com All rights reserved.
 */
package com.jeesite.modules.sys.service.support;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

import javax.validation.ConstraintViolationException;

import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;
import org.springframework.web.multipart.MultipartFile;

import com.jeesite.common.cache.CacheUtils;
import com.jeesite.common.entity.Extend;
import com.jeesite.common.entity.Page;
import com.jeesite.common.idgen.IdGen;
import com.jeesite.common.lang.DateUtils;
import com.jeesite.common.lang.StringUtils;
import com.jeesite.common.service.CrudService;
import com.jeesite.common.service.ServiceException;
import com.jeesite.common.utils.IdcardUtils;
import com.jeesite.common.utils.excel.ExcelImport;
import com.jeesite.common.validator.ValidatorUtils;
import com.jeesite.modules.edu.dao.EduIndustryDao;
import com.jeesite.modules.edu.dao.EduProfessionalTitleDao;
import com.jeesite.modules.edu.dao.EduSubjectDao;
import com.jeesite.modules.edu.dao.EduTalentDao;
import com.jeesite.modules.edu.dao.EduTeacherStateDao;
import com.jeesite.modules.edu.dao.EduTeacherTitleDao;
import com.jeesite.modules.edu.entity.EduIndustry;
import com.jeesite.modules.edu.entity.EduProfessionalTitle;
import com.jeesite.modules.edu.entity.EduSubject;
import com.jeesite.modules.edu.entity.EduTalent;
import com.jeesite.modules.edu.entity.EduTeacherState;
import com.jeesite.modules.edu.entity.EduTeacherTitle;
import com.jeesite.modules.edu.entity.EduTwoOrganization;
import com.jeesite.modules.sys.dao.AreaDao;
import com.jeesite.modules.sys.dao.CompanyDao;
import com.jeesite.modules.sys.dao.EmpUserDao;
import com.jeesite.modules.sys.dao.EmployeeDao;
import com.jeesite.modules.sys.dao.OfficeDao;
import com.jeesite.modules.sys.dao.PostDao;
import com.jeesite.modules.sys.dao.UserRoleDao;
import com.jeesite.modules.sys.entity.Area;
import com.jeesite.modules.sys.entity.Company;
import com.jeesite.modules.sys.entity.EmpUser;
import com.jeesite.modules.sys.entity.Employee;
import com.jeesite.modules.sys.entity.Office;
import com.jeesite.modules.sys.entity.Post;
import com.jeesite.modules.sys.entity.User;
import com.jeesite.modules.sys.entity.UserRole;
import com.jeesite.modules.sys.service.EmpUserService;
import com.jeesite.modules.sys.service.EmployeeService;
import com.jeesite.modules.sys.service.UserService;
import com.jeesite.modules.sys.utils.DictUtils;
import com.jeesite.modules.sys.utils.EmpUtils;
import com.jeesite.modules.sys.utils.UserUtils;

/**
 * 员工管理Service
 * @author ThinkGem
 * @version 2017-03-25
 */
@Transactional(readOnly=true)
public class EmpUserServiceSupport extends CrudService<EmpUserDao, EmpUser>
		implements EmpUserService{

	@Autowired
	private UserService userService;
	
	@Autowired
	private EmployeeService employeeService;
	@Autowired
	private EduTalentDao eduTalentDao;
	
	@Autowired
	private UserRoleDao userRoleDao;
	@Autowired
	private AreaDao areaDao;
	@Autowired
	private CompanyDao companyDao;
	@Autowired
	private EduTeacherStateDao eduTeacherStateDao;
	@Autowired
	private EduTeacherTitleDao eduTeacherTitleDao;
	@Autowired
	private OfficeDao officeDao;
	
	
	@Autowired
	private EduProfessionalTitleDao eduProfessionalTitleDao;
	@Autowired
	private EduSubjectDao eduSubjectDao;
	@Autowired
	private EduIndustryDao eduIndustryDao;

	@Autowired
	private PostDao postDao;
	@Autowired
	private EmpUserDao empUserDao;
	@Autowired
	private EmployeeDao employeeDao;
	@Transactional(readOnly=false)
	public void updated(EmpUser empUser) {
		empUserDao.updated(empUser);
		employeeDao.updated(empUser.getEmployee());
	}
	
	/**
	 * 获取单条数据
	 */
	@Override
	public EmpUser get(EmpUser empUser) {
		EmpUser entity = super.get(empUser);
		if (entity != null){
			Employee employee = entity.getEmployee();
			EduTalent eduTalent = new EduTalent();
			eduTalent.setEmployee(employee);
			eduTalent.setStatus(EduTalent.STATUS_NORMAL);
			employee.setTestDataChildList(eduTalentDao.findList(eduTalent));
			EduTeacherState eduTeacherState = new EduTeacherState();
			eduTeacherState.setUser(entity);
			employee.setTeacherStateChildList(eduTeacherStateDao.findList(eduTeacherState));
		}
		return entity;
	}
	
	/**
	 * 添加数据权限过滤条件
	 * @param entity 控制对象
	 * @param ctrlPermi 控制权限类型（拥有的数据权限：DataScope.CTRL_PERMI_HAVE、可管理的数据权限：DataScope.CTRL_PERMI_HAVE）
	 */
	@Override
	public void addDataScopeFilter(EmpUser empUser, String ctrlPermi) {
		boolean ret = true;
		User user = UserUtils.getUser();
		if(!user.isAdmin()) {
			String loginAdmin = user.getLoginCode();
			if (loginAdmin.indexOf("MANAGER")>-1) {
				ret = false;
			}
		}
		if(ret) {
			empUser.getSqlMap().getDataScope().addFilter("dsfOffice",
					"Office", "e.office_code", "a.create_by", ctrlPermi);
		}
		
		if (StringUtils.isNotBlank(EmpUtils.getCompany().getCompanyCode())){
			empUser.getSqlMap().getDataScope().addFilter("dsfCompany",
					"Company", "e.company_code", "a.create_by", ctrlPermi);
		}
		
	}

	/**
	 * 分页查询数据
	 */
	@Override
	public Page<EmpUser> findPage(EmpUser empUser) {
		return super.findPage(empUser);
	}

	/**
	 * 保存用户员工
	 */
	@Override
	@Transactional(readOnly=false)
	public void save(EmpUser user) {
		User u = UserUtils.getByLoginCode(user.getLoginCode());
		if (!org.springframework.util.StringUtils.isEmpty(u)
				&& !org.springframework.util.StringUtils.isEmpty(u.getUserCode())) {
			user.setUserCode(u.getUserCode());
		}
		// 1、初始化用户信息
		if (user.getIsNewRecord()){
			userService.genId(user, user.getLoginCode());
			user.setUserCode(user.getUserCode()+"_"+IdGen.randomBase62(4).toLowerCase());
			user.setUserType(EmpUser.USER_TYPE_EMPLOYEE);
			user.setMgrType(EmpUser.MGR_TYPE_NOT_ADMIN);
		}
		Employee employee = user.getEmployee();
		// 如果员工编码为空，则使用用户编码
		if (StringUtils.isBlank(employee.getEmpCode())){
			employee.setEmpCode(user.getUserCode());
		}
		// 如果员工姓名为空，则使用昵称名
		if (StringUtils.isBlank(employee.getEmpName())){
			employee.setEmpName(user.getUserName());
		}
		// 2、保存用户
		user.setRefCode(employee.getEmpCode());
		user.setRefName(employee.getEmpName());
		logger.info("准备执行保存用户操作...");
		try {
			userService.save(user);
		}catch(Exception e) {
			e.getMessage();
			e.printStackTrace();
		}
		logger.info("准备结束保存用户操作----------------------------------------------------");
		
		// 3、保存员工
		employee.setIsNewRecord(user.getIsNewRecord());
		employeeService.save(employee);
		if(employee.getIsNewRecord()) {
			UserRole userRole = new UserRole();
			userRole.setUserCode(user.getUserCode());
			userRole.setRoleCode("teacher");
			userRoleDao.insert(userRole);
		}
		
		if(!CollectionUtils.isEmpty(employee.getTestDataChildList())) {
			for (EduTalent testDataChild : employee.getTestDataChildList()){
				if (!EduTalent.STATUS_DELETE.equals(testDataChild.getStatus())){
					testDataChild.setEmployee(employee);
					if (testDataChild.getIsNewRecord()){
						testDataChild.preInsert();
						eduTalentDao.insert(testDataChild);
					}else{
						testDataChild.preUpdate();
						eduTalentDao.update(testDataChild);
					}
				}else{
					eduTalentDao.delete(testDataChild);
				}
			}
		}
		
		
	}

	/**
	 * 导入用户数据
	 * @param file 导入的用户数据文件
	 * @param isUpdateSupport 是否更新支持，如果已存在，则进行更新数据
	 */
	@Transactional(readOnly=false)
	public String importData(MultipartFile file, Boolean isUpdateSupport) {
		if (file == null){
			throw new ServiceException("请选择导入的数据文件！");
		}
		int successNum = 0; int failureNum = 0;
		StringBuilder successMsg = new StringBuilder();
		StringBuilder failureMsg = new StringBuilder();
		try(ExcelImport ei = new ExcelImport(file, 2, 0)){
			List<EmpUser> list = ei.getDataList(EmpUser.class);
			for (EmpUser user : list) {
				try{
					if(org.springframework.util.StringUtils.isEmpty(user.getLoginCode())||org.springframework.util.StringUtils.isEmpty(user.getUserName())) {
						continue;
					}
					// 验证数据文件
					ValidatorUtils.validateWithException(user);
					// 验证是否存在这个用户
					User u = UserUtils.getByLoginCode(user.getLoginCode());
					if (u == null){
						this.save(user);
						successNum++;
						successMsg.append("<br/>" + successNum + "、账号 " + user.getLoginCode() + " 导入成功");
					} else if (isUpdateSupport){
						this.save(user);

						successNum++;
						successMsg.append("<br/>" + successNum + "、账号 " + user.getLoginCode() + " 更新成功");
					} else {
						failureNum++;
						failureMsg.append("<br/>" + failureNum + "、账号 " + user.getLoginCode() + " 已存在");
					}
				} catch (Exception e) {
					failureNum++;
					String msg = "<br/>" + failureNum + "、账号 " + user.getLoginCode() + " 导入失败：";
					if (e instanceof ConstraintViolationException){
						List<String> messageList = ValidatorUtils.extractPropertyAndMessageAsList((ConstraintViolationException)e, ": ");
						for (String message : messageList) {
							msg += message + "; ";
						}
					}else{
						msg += e.getMessage();
					}
					failureMsg.append(msg);
					logger.error(msg, e);
				}
			}
		} catch (Exception e) {
			failureMsg.append(e.getMessage());
			logger.error(e.getMessage(), e);
		}
		if (failureNum > 0) {
			failureMsg.insert(0, "很抱歉，导入失败！共 " + failureNum + " 条数据格式不正确，错误如下：");
			throw new ServiceException(failureMsg.toString());
		}else{
			successMsg.insert(0, "恭喜您，数据已全部导入成功！共 " + successNum + " 条，数据如下：");
		}
		return successMsg.toString();
	}

	/**
	 * 更新状态
	 */
	@Override
	@Transactional(readOnly=false)
	public void updateStatus(EmpUser empUser) {
		userService.delete(empUser);
		employeeService.delete(empUser.getEmployee());
	}
	
	/**
	 * 删除用户
	 */
	@Override
	@Transactional(readOnly=false)
	public void delete(EmpUser empUser) {
		userService.delete(empUser);
		employeeService.delete(empUser.getEmployee());
	}
	
	public static <T> List<List<T>> averageAssign(List<T> source, int n) {
	    List<List<T>> result = new ArrayList<List<T>>();
	    int remainder = source.size() % n;  //(先计算出余数)
	    int number = source.size() / n;  //然后是商
	    int offset = 0;//偏移量
	    for (int i = 0; i < n; i++) {
	        List<T> value = null;
	        if (remainder > 0) {
	            value = source.subList(i * number + offset, (i + 1) * number + offset + 1);
	            remainder--;
	            offset++;
	        } else {
	            value = source.subList(i * number + offset, (i + 1) * number + offset);
	        }
	        result.add(value);
	    }
	    return result;
	}
	
public void averageAssign(EmpUser empUser, Boolean isAll, String ctrlPermi) {
		
		String userCode = null;
		try {
			
			
			List<EmpUser> ss = findList(empUser);
			List<List<EmpUser>> groups = averageAssign(ss,10);
			int k = 1;
			for(List<EmpUser> list : groups) {
				InputStream is = this.getClass().getResourceAsStream("/config/excel.xlsx");
				XSSFWorkbook wb = new XSSFWorkbook(is);
				XSSFSheet sheet = wb.getSheetAt(0);
				addDataScopeFilter(empUser, ctrlPermi);
				
			int i=1;
			int j=1;
			for(EmpUser user : list) {
				System.out.println("------------------------------------------------>"+ i);
				userCode = user.getUserCode();
				Employee employee = employeeService.get(user.getEmployee());
				Office office = employee.getOffice();
				if(org.springframework.util.StringUtils.isEmpty(office)) {
					logger.info("当前用户为选择供职院校{}",user.getUserName());
				}
				Company company = employee.getCompany();
				Extend extend = user.getExtend();
				XSSFRow row = sheet.createRow(j);
				XSSFCell  serialNumberCell = row.createCell(0);
				serialNumberCell.setCellValue(i);
				
				//姓名
				XSSFCell  userNameCell = row.createCell(1);
				userNameCell.setCellValue(user.getUserName());
				
				//身份证号
				String idCard = user.getLoginCode();
				XSSFCell  idCardCell = row.createCell(2);
				idCardCell.setCellValue(idCard);
				if(IdcardUtils.validateCard(idCard)) {
					//性别
					if(IdcardUtils.validateCard(idCard)) {
						XSSFCell  sexCell = row.createCell(3);
						sexCell.setCellValue(IdcardUtils.getChineseGenderByIdCard(idCard));
					}
					//年龄
					if(IdcardUtils.validateCard(idCard)) {
						XSSFCell  sexCell = row.createCell(4);
						sexCell.setCellValue(IdcardUtils.getAgeByIdCard(idCard));
						/*
						if(IdcardUtils.validateCard(idCard)) {
							XSSFCell  birthdayCell = row.createCell(4);
							birthdayCell.setCellValue(IdcardUtils.getBirthByIdCard(idCard));
						}
						*/
						
					}
					
				}
				
				//手机号
				XSSFCell  mobileCell = row.createCell(5);
				mobileCell.setCellValue(user.getMobile());
				
				if(!org.springframework.util.StringUtils.isEmpty(extend)) {
					
					//国籍
					String countryCode = extend.getExtendS1();
					if(!org.springframework.util.StringUtils.isEmpty(countryCode)) {
						XSSFCell  countryCell = row.createCell(6);
						String value = CacheUtils.get("js_sys_country", countryCode);
						if(org.springframework.util.StringUtils.isEmpty(value)) {
							value = DictUtils.getDictLabel("js_sys_country", countryCode, "");
							CacheUtils.put("js_sys_country", countryCode, value);
						}
						countryCell.setCellValue(value);
					}
					
					//民族
					String nationCode = extend.getExtendS2();
					if(!org.springframework.util.StringUtils.isEmpty(nationCode)) {
						XSSFCell  nationCell = row.createCell(7);
						String value = CacheUtils.get("nation", nationCode);
						if(org.springframework.util.StringUtils.isEmpty(value)) {
							value = DictUtils.getDictLabel("nation", nationCode, "");
							CacheUtils.put("nation", nationCode, value);
						}
						nationCell.setCellValue(value);
					}
					//政治面貌
					String politicalCode = extend.getExtendS3();
					if(!org.springframework.util.StringUtils.isEmpty(politicalCode)) {
						XSSFCell  politicalCell = row.createCell(8);
						String value = CacheUtils.get("political", politicalCode);
						if(org.springframework.util.StringUtils.isEmpty(value)) {
							value = DictUtils.getDictLabel("political", politicalCode, "");
							CacheUtils.put("political", politicalCode, value);
						}
						politicalCell.setCellValue(value);
					}
					
					
				}
				
				//所在内设机构名称
				EduTwoOrganization eduTwoOrganization = employee.getEduTwoOrganization();
				if(!org.springframework.util.StringUtils.isEmpty(eduTwoOrganization)) {
					XSSFCell  eduTwoOrganizationCell = row.createCell(9);
					eduTwoOrganizationCell.setCellValue(eduTwoOrganization.getDictLabel());		
				}
				//所在内设机构职务
				String organization = employee.getOrganization();
				XSSFCell  organizationCell = row.createCell(10);
				organizationCell.setCellValue(organization);
				//所聘岗位
			
			
					Post p = new Post();
					p.setEmpCode(employee.getEmpCode());
					List<Post> posts = postDao.findList(p);
					if(!CollectionUtils.isEmpty(posts)) {
						StringBuffer pb = new StringBuffer();
						for(Post post:posts) {
							pb.append(post.getPostName());
							pb.append(",");
						}
						String pVal = pb.toString();
						XSSFCell  postCell = row.createCell(11);
						postCell.setCellValue(pVal.substring(0, pVal.length() - 1));
					}
					
				
				
				Company officeCompanyQuery = new Company();
				officeCompanyQuery.setCompanyCode(office.getOfficeCode());
				Company officeCompany = CacheUtils.get("company",office.getOfficeCode());
				if(org.springframework.util.StringUtils.isEmpty(officeCompany)) {
					officeCompany = companyDao.get(officeCompanyQuery);
				}
				
				if(!org.springframework.util.StringUtils.isEmpty(officeCompany)) {
					CacheUtils.put("company",office.getOfficeCode(), officeCompany);
					Area area = officeCompany.getArea();
					if(!org.springframework.util.StringUtils.isEmpty(area)) {
						XSSFCell  officeAreaCell = row.createCell(12);
						officeAreaCell.setCellValue(area.getTreeNames());
					}
				}
				
				//供职院校
				XSSFCell  officeCell = row.createCell(13);
				officeCell.setCellValue(office.getOfficeName());
				
				
				if(!org.springframework.util.StringUtils.isEmpty(office)) {
					Office entity = CacheUtils.get("office",office.getId());
					if(org.springframework.util.StringUtils.isEmpty(entity)) {
						entity = officeDao.get(office);
					}
					if(!org.springframework.util.StringUtils.isEmpty(entity)) {

						CacheUtils.put("office",office.getId(),entity);
						Extend officeExtend = entity.getExtend();
						if(!org.springframework.util.StringUtils.isEmpty(officeExtend)) {
							//办学层次
							String arrangement = officeExtend.getExtendS3();
							XSSFCell  arrangementCell = row.createCell(14);
							arrangementCell.setCellValue(arrangement);
							//办学性质
							String nature = officeExtend.getExtendS4();
							XSSFCell  natureCell = row.createCell(15);
							natureCell.setCellValue(nature);
						}
					}
				}
				
				//学历
				String highestEdu = CacheUtils.get("highest_edu", employee.getHighestEdu());
				if(org.springframework.util.StringUtils.isEmpty(highestEdu)) {
					highestEdu = DictUtils.getDictLabel("highest_edu", employee.getHighestEdu(), "");
					CacheUtils.put("highest_edu", employee.getHighestEdu(), highestEdu);
				}
				
				XSSFCell  highestEduCell = row.createCell(16);
				highestEduCell.setCellValue(highestEdu);
				
				//最高毕业院校
				XSSFCell  companyCell = row.createCell(17);
				if(!org.springframework.util.StringUtils.isEmpty(company)) {
					if(!org.springframework.util.StringUtils.isEmpty(company.getId())) {
						if(company.getId().equals("4265051061")||company.getId().equals("4265051062")||company.getId().equals("4265051063")) {
							companyCell.setCellValue(employee.getCompany().getCompanyName());
						}else {
							companyCell.setCellValue(company.getCompanyName());
						}
					}
					
					//毕业院校所在省
					if(!org.springframework.util.StringUtils.isEmpty(company)) {
						Area areaQuery = new Area();
						if(!org.springframework.util.StringUtils.isEmpty(company.getArea())&&!org.springframework.util.StringUtils.isEmpty(company.getArea().getAreaCode())) {
							String areaCode = company.getArea().getAreaCode().substring(0,2)+"0000";
							areaQuery.setAreaCode(areaCode);
							Area area = CacheUtils.get(areaCode);
							if(org.springframework.util.StringUtils.isEmpty(area)) {
								area = areaDao.get(areaQuery);
								CacheUtils.put(areaCode, area);
							}
							if(!org.springframework.util.StringUtils.isEmpty(area)) {
								XSSFCell  officeAreaCell = row.createCell(18);
								officeAreaCell.setCellValue(area.getTreeNames());
							}
							//所在市
							if(!org.springframework.util.StringUtils.isEmpty(company.getArea())) {
								XSSFCell  officeAreaCell = row.createCell(19);
								officeAreaCell.setCellValue(company.getArea().getTreeNames());
							}
						}
						
					}
				}
				
				
				//学位
				String highestDegree = CacheUtils.get("highest_degree", employee.getHighestDegree());
				if(org.springframework.util.StringUtils.isEmpty(highestDegree)) {
					highestDegree = DictUtils.getDictLabel("highest_degree", employee.getHighestDegree(), "");
					CacheUtils.put("highest_degree", employee.getHighestDegree(), highestDegree);
				}
				
				XSSFCell  highestDegreeCell = row.createCell(20);
				highestDegreeCell.setCellValue(highestDegree);
				
				//所教授的学生层次
				String studentLevel = CacheUtils.get("sys_student_level", employee.getStudentLevel());
				if(org.springframework.util.StringUtils.isEmpty(studentLevel)) {
					studentLevel = DictUtils.getDictLabel("sys_student_level", employee.getStudentLevel(), "");
					CacheUtils.put("sys_student_level", employee.getStudentLevel(), studentLevel);
				}
				
				XSSFCell  studentLevelCell = row.createCell(21);
				studentLevelCell.setCellValue(studentLevel);
				 
				//学习
				String learningSituation = CacheUtils.get("school_abroad", employee.getLearningSituation());
				if(org.springframework.util.StringUtils.isEmpty(learningSituation)) {
					learningSituation = DictUtils.getDictLabel("school_abroad", employee.getLearningSituation(), "");
					CacheUtils.put("school_abroad", employee.getLearningSituation(), learningSituation);
				}
				
				
				XSSFCell  learningSituationCell = row.createCell(22);
				learningSituationCell.setCellValue(learningSituation);
				XSSFCell  learningSituationExtCell = row.createCell(23);
				learningSituationExtCell.setCellValue(employee.getLearningSituationExt());
				
				//工作
				String workJob = CacheUtils.get("work_abroad", employee.getWorkJob());
				if(org.springframework.util.StringUtils.isEmpty(workJob)) {
					workJob = DictUtils.getDictLabel("work_abroad", employee.getWorkJob(), "");
					CacheUtils.put("work_abroad", employee.getWorkJob(), workJob);
				}
				
				XSSFCell  workJobCell = row.createCell(24);
				workJobCell.setCellValue(workJob);
				XSSFCell  workJobExtCell = row.createCell(25);
				workJobExtCell.setCellValue(employee.getWorkJobExt());
				
				//专业技术职务1
				EduTeacherTitle tt = employee.getEduTeacherTitle();
				if(!org.springframework.util.StringUtils.isEmpty(tt)) {
					EduTeacherTitle eduTeacherTitle = CacheUtils.get("ett",tt.getId());
					if(org.springframework.util.StringUtils.isEmpty(eduTeacherTitle)) {
						eduTeacherTitle = eduTeacherTitleDao.get(tt);
						if(!org.springframework.util.StringUtils.isEmpty(eduTeacherTitle)) {
						}
					}
					
					if(!org.springframework.util.StringUtils.isEmpty(eduTeacherTitle)) {
						CacheUtils.put("ett",tt.getId(),eduTeacherTitle);

						String[] eduTeacherTitles = eduTeacherTitle.getTreeNames().split("/");
						pullCell(row,26,eduTeacherTitles);
					}
				}
				
			
				EduProfessionalTitle pt = employee.getEduProfessionalTitle();
				if(!org.springframework.util.StringUtils.isEmpty(pt)) {
					EduProfessionalTitle eduProfessionalTitle = CacheUtils.get("ept",pt.getId());
					if(org.springframework.util.StringUtils.isEmpty(eduProfessionalTitle)) {
						eduProfessionalTitle = eduProfessionalTitleDao.get(pt);
					}
					
					if(!org.springframework.util.StringUtils.isEmpty(eduProfessionalTitle)) {
						CacheUtils.put("ept",pt.getId(),eduProfessionalTitle);
						String[] professionalTitles = eduProfessionalTitle.getTreeNames().split("/");
						pullCell(row,29,professionalTitles,eduProfessionalTitle.getRemarks());
					}
				}

				EduSubject es = employee.getSubjectResearch();
				if(!org.springframework.util.StringUtils.isEmpty(es)) {
					EduSubject eduSubject = CacheUtils.get("subject",es.getId());
					if(org.springframework.util.StringUtils.isEmpty(eduSubject)) {
						eduSubject = eduSubjectDao.get(es);
					}
					
					if(!org.springframework.util.StringUtils.isEmpty(eduSubject)) {
						CacheUtils.put("subject",es.getId(),eduSubject);
						String[] eduSubjects = eduSubject.getTreeNames().split("/");
						pullCell(row,32,eduSubjects);
					}
					
				}
				
				EduIndustry ei = employee.getEduIndustry();
				
				if(!org.springframework.util.StringUtils.isEmpty(ei)) {
					EduIndustry eduIndustry = CacheUtils.get("industry",ei.getId());
					if(org.springframework.util.StringUtils.isEmpty(eduIndustry)) {
						eduIndustry = eduIndustryDao.get(ei);
					}
					
					if(!org.springframework.util.StringUtils.isEmpty(eduIndustry)) {
						CacheUtils.put("industry",ei.getId(),eduIndustry);
						String[] eduIndustrys = eduIndustry.getTreeNames().split("/");
						pullCell(row,36,eduIndustrys);
					}
				}
				
				//是否博士后
				
				String yes = CacheUtils.get("sys_yes_no", employee.getIsPostdoctor());
				if(org.springframework.util.StringUtils.isEmpty(yes)) {
					yes = DictUtils.getDictLabel("sys_yes_no", employee.getIsPostdoctor(), "");
					CacheUtils.put("sys_yes_no", employee.getIsPostdoctor(), yes);
				}
				
				XSSFCell  yesCell = row.createCell(40);
				yesCell.setCellValue(yes);
				//担任导师
				
				String mentor = CacheUtils.get("sys_mentor", employee.getMentor());
				if(org.springframework.util.StringUtils.isEmpty(mentor)) {
					mentor = DictUtils.getDictLabel("sys_mentor", employee.getMentor(),  "");
					CacheUtils.put("sys_mentor", employee.getMentor(), mentor);
				}
				
				XSSFCell  mentorCell = row.createCell(41);
				mentorCell.setCellValue(mentor);
				//最高学历所学专业
				EduSubject em = employee.getSubjectMajor();
				if(!org.springframework.util.StringUtils.isEmpty(em)) {
					EduSubject subjectMajor = CacheUtils.get("subject",em.getId());
					if(org.springframework.util.StringUtils.isEmpty(subjectMajor)) {
						subjectMajor = eduSubjectDao.get(em);

					}
					if(!org.springframework.util.StringUtils.isEmpty(subjectMajor)) {
						CacheUtils.put("subject",em.getId(),subjectMajor);
						XSSFCell  subjectMajorCell = row.createCell(42);
						subjectMajorCell.setCellValue(subjectMajor.getDictLabel());
					}
				}

				//人才称号
				EduTalent eduTalentQuery = new EduTalent();
				eduTalentQuery.setEmployee(employee);
				List<EduTalent> eduTalents = eduTalentDao.findList(eduTalentQuery);
				
				
				StringBuilder sb = new StringBuilder();
				List<Integer> rets = new ArrayList<Integer>();
				int cnt = 0;
				for(EduTalent eduTalent :eduTalents) {
					
					String value = CacheUtils.get("sys_talent", eduTalent.getTalentCode());
					if(org.springframework.util.StringUtils.isEmpty(value)) {
						value = DictUtils.getDictLabel("sys_talent", eduTalent.getTalentCode(),  "");
						CacheUtils.put("sys_talent", eduTalent.getTalentCode(), value);
					}
					
					if(!org.springframework.util.StringUtils.isEmpty(eduTalent.getTalentCode())) {
						int code = Integer.valueOf(eduTalent.getTalentCode());
						rets.add(code);
					}
					sb.append(eduTalent.getTalentYear() + "   "  +value);
					sb.append("\n");
					cnt++;
				}
				int ret = 99;
				if(!CollectionUtils.isEmpty(rets)) {
					ret = Collections.min(rets);
				}
				String title = "基础人才";
				if (ret > 0 && ret < 22) {
					title = "高端人才";
				} else if (ret > 21 && ret < 37) {
					title = "突出人才";
				}
				
				if(org.springframework.util.StringUtils.isEmpty(title)) {
					if(employee.getIsPostdoctor().equals("1")) {
						title = "骨干人才";
					}
				}
				
				//入选年份人才称号
				XSSFCell  eduTalentCell = row.createCell(43);
				eduTalentCell.setCellValue(sb.toString());
				
				String schoolJob = CacheUtils.get("school_job", employee.getSchoolJob());
				if(org.springframework.util.StringUtils.isEmpty(schoolJob)) {
					schoolJob = DictUtils.getDictLabel("school_job", employee.getSchoolJob(),  "");
					CacheUtils.put("school_job", employee.getSchoolJob(), schoolJob);
				}
				
				XSSFCell  schoolJobCell = row.createCell(44);
				schoolJobCell.setCellValue(schoolJob);
				XSSFCell  schoolJobExtCell = row.createCell(45);
				schoolJobExtCell.setCellValue(employee.getSchoolJobExt());
				
				String industryJob = CacheUtils.get("industry_job", employee.getIndustryJob());
				if(org.springframework.util.StringUtils.isEmpty(industryJob)) {
					industryJob = DictUtils.getDictLabel("industry_job", employee.getIndustryJob(),  "");
					CacheUtils.put("industry_job", employee.getIndustryJob(), industryJob);
				}
				
				XSSFCell  industryJobCell = row.createCell(46);
				industryJobCell.setCellValue(industryJob);
				XSSFCell  industryJobExtCell = row.createCell(47);
				industryJobExtCell.setCellValue(employee.getIndustryJobExt());
				
				XSSFCell  titleCell = row.createCell(48);
				titleCell.setCellValue(title);
				
				XSSFCell  cntCell = row.createCell(49);
				cntCell.setCellValue(cnt);
				//46人才称号精选
				//47人才称号人数
				//47-66
                    
				
                sheet.addMergedRegion(new CellRangeAddress(j,j,50,68));
				
                XSSFCell  totalCell = row.createCell(50);
                totalCell.setCellValue(sb.toString());
				
				//职称类别+4 16 17 18 19
//				String postCode = employee.getPostCode();
//				if(!org.springframework.util.StringUtils.isEmpty(postCode)) {
//					XSSFCell  postCell = row.createCell(20);
//					postCell.setCellValue(yes);
//				}
//				
				
				i++;
				j++;
			}
				String dd = DateUtils.getDate() + "-" +  k;
				File file = new File("/home/git/jeesite4/upload" + dd + ".xlsx");
				
				if (!file.exists()) {
					file.createNewFile();
				}
				k++;
				FileOutputStream out = new FileOutputStream(file);
				wb.write(out);
				wb.close();
				out.flush();
				out.close();
				
			}
		} catch (IOException e) {
			e.printStackTrace();
			logger.error("异常信息~userCode:{}",userCode);
		}finally {
//			try {
//				if(is!=null) {
//					is.close();
//				}
//			} catch (IOException e) {
//				e.printStackTrace();
//			}
		}
	}
	
	
	public XSSFWorkbook exportData(EmpUser empUser, Boolean isAll, String ctrlPermi) {
		InputStream is = this.getClass().getResourceAsStream("/config/excel.xlsx");
		XSSFWorkbook wb = null;
		String userCode = null;
		try {
			wb = new XSSFWorkbook(is);
			XSSFSheet sheet = wb.getSheetAt(0);
			addDataScopeFilter(empUser, ctrlPermi);
			List<EmpUser> list = findList(empUser);
			int i=1;
			int j=1;
			for(EmpUser user : list) {
				System.out.println("------------------------------------------------>"+ i);
				userCode = user.getUserCode();
				Employee employee = employeeService.get(user.getEmployee());
				Office office = employee.getOffice();
				if(org.springframework.util.StringUtils.isEmpty(office)) {
					logger.info("当前用户为选择供职院校{}",user.getUserName());
				}
				Company company = employee.getCompany();
				Extend extend = user.getExtend();
				XSSFRow row = sheet.createRow(j);
				XSSFCell  serialNumberCell = row.createCell(0);
				serialNumberCell.setCellValue(i);
				
				//姓名
				XSSFCell  userNameCell = row.createCell(1);
				userNameCell.setCellValue(user.getUserName());
				
				//身份证号
				String idCard = user.getLoginCode();
				XSSFCell  idCardCell = row.createCell(2);
				idCardCell.setCellValue(idCard);
				if(IdcardUtils.validateCard(idCard)) {
					//性别
					if(IdcardUtils.validateCard(idCard)) {
						XSSFCell  sexCell = row.createCell(3);
						sexCell.setCellValue(IdcardUtils.getChineseGenderByIdCard(idCard));
					}
					//年龄
					if(IdcardUtils.validateCard(idCard)) {
						XSSFCell  sexCell = row.createCell(4);
						sexCell.setCellValue(IdcardUtils.getAgeByIdCard(idCard));
						/*
						if(IdcardUtils.validateCard(idCard)) {
							XSSFCell  birthdayCell = row.createCell(4);
							birthdayCell.setCellValue(IdcardUtils.getBirthByIdCard(idCard));
						}
						*/
						
					}
					
				}
				
				//手机号
				XSSFCell  mobileCell = row.createCell(5);
				mobileCell.setCellValue(user.getMobile());
				
				if(!org.springframework.util.StringUtils.isEmpty(extend)) {
					
					//国籍
					String countryCode = extend.getExtendS1();
					if(!org.springframework.util.StringUtils.isEmpty(countryCode)) {
						XSSFCell  countryCell = row.createCell(6);
						String value = CacheUtils.get("js_sys_country", countryCode);
						if(org.springframework.util.StringUtils.isEmpty(value)) {
							value = DictUtils.getDictLabel("js_sys_country", countryCode, "");
							CacheUtils.put("js_sys_country", countryCode, value);
						}
						countryCell.setCellValue(value);
					}
					
					//民族
					String nationCode = extend.getExtendS2();
					if(!org.springframework.util.StringUtils.isEmpty(nationCode)) {
						XSSFCell  nationCell = row.createCell(7);
						String value = CacheUtils.get("nation", nationCode);
						if(org.springframework.util.StringUtils.isEmpty(value)) {
							value = DictUtils.getDictLabel("nation", nationCode, "");
							CacheUtils.put("nation", nationCode, value);
						}
						nationCell.setCellValue(value);
					}
					//政治面貌
					String politicalCode = extend.getExtendS3();
					if(!org.springframework.util.StringUtils.isEmpty(politicalCode)) {
						XSSFCell  politicalCell = row.createCell(8);
						String value = CacheUtils.get("political", politicalCode);
						if(org.springframework.util.StringUtils.isEmpty(value)) {
							value = DictUtils.getDictLabel("political", politicalCode, "");
							CacheUtils.put("political", politicalCode, value);
						}
						politicalCell.setCellValue(value);
					}
					
					
				}
				
				//所在内设机构名称
				EduTwoOrganization eduTwoOrganization = employee.getEduTwoOrganization();
				if(!org.springframework.util.StringUtils.isEmpty(eduTwoOrganization)) {
					XSSFCell  eduTwoOrganizationCell = row.createCell(9);
					eduTwoOrganizationCell.setCellValue(eduTwoOrganization.getDictLabel());		
				}
				//所在内设机构职务
				String organization = employee.getOrganization();
				XSSFCell  organizationCell = row.createCell(10);
				organizationCell.setCellValue(organization);
				//所聘岗位
			
			
					Post p = new Post();
					p.setEmpCode(employee.getEmpCode());
					List<Post> posts = postDao.findList(p);
					if(!CollectionUtils.isEmpty(posts)) {
						StringBuffer pb = new StringBuffer();
						for(Post post:posts) {
							pb.append(post.getPostName());
							pb.append(",");
						}
						String pVal = pb.toString();
						XSSFCell  postCell = row.createCell(11);
						postCell.setCellValue(pVal.substring(0, pVal.length() - 1));
					}
					
				
				
				Company officeCompanyQuery = new Company();
				officeCompanyQuery.setCompanyCode(office.getOfficeCode());
				Company officeCompany = CacheUtils.get("company",office.getOfficeCode());
				if(org.springframework.util.StringUtils.isEmpty(officeCompany)) {
					officeCompany = companyDao.get(officeCompanyQuery);
				}
				
				if(!org.springframework.util.StringUtils.isEmpty(officeCompany)) {
					CacheUtils.put("company",office.getOfficeCode(), officeCompany);
					Area area = officeCompany.getArea();
					if(!org.springframework.util.StringUtils.isEmpty(area)) {
						XSSFCell  officeAreaCell = row.createCell(12);
						officeAreaCell.setCellValue(area.getTreeNames());
					}
				}
				
				//供职院校
				XSSFCell  officeCell = row.createCell(13);
				officeCell.setCellValue(office.getOfficeName());
				
				
				if(!org.springframework.util.StringUtils.isEmpty(office)) {
					Office entity = CacheUtils.get("office",office.getId());
					if(org.springframework.util.StringUtils.isEmpty(entity)) {
						entity = officeDao.get(office);
					}
					if(!org.springframework.util.StringUtils.isEmpty(entity)) {

						CacheUtils.put("office",office.getId(),entity);
						Extend officeExtend = entity.getExtend();
						if(!org.springframework.util.StringUtils.isEmpty(officeExtend)) {
							//办学层次
							String arrangement = officeExtend.getExtendS3();
							XSSFCell  arrangementCell = row.createCell(14);
							arrangementCell.setCellValue(arrangement);
							//办学性质
							String nature = officeExtend.getExtendS4();
							XSSFCell  natureCell = row.createCell(15);
							natureCell.setCellValue(nature);
						}
					}
				}
				
				//学历
				String highestEdu = CacheUtils.get("highest_edu", employee.getHighestEdu());
				if(org.springframework.util.StringUtils.isEmpty(highestEdu)) {
					highestEdu = DictUtils.getDictLabel("highest_edu", employee.getHighestEdu(), "");
					CacheUtils.put("highest_edu", employee.getHighestEdu(), highestEdu);
				}
				
				XSSFCell  highestEduCell = row.createCell(16);
				highestEduCell.setCellValue(highestEdu);
				
				//最高毕业院校
				XSSFCell  companyCell = row.createCell(17);
				if(!org.springframework.util.StringUtils.isEmpty(company)) {
					if(!org.springframework.util.StringUtils.isEmpty(company.getId())) {
						if(company.getId().equals("4265051061")||company.getId().equals("4265051062")||company.getId().equals("4265051063")) {
							companyCell.setCellValue(employee.getCompany().getCompanyName());
						}else {
							companyCell.setCellValue(company.getCompanyName());
						}
					}
					
					//毕业院校所在省
					if(!org.springframework.util.StringUtils.isEmpty(company)) {
						Area areaQuery = new Area();
						if(!org.springframework.util.StringUtils.isEmpty(company.getArea())&&!org.springframework.util.StringUtils.isEmpty(company.getArea().getAreaCode())) {
							String areaCode = company.getArea().getAreaCode().substring(0,2)+"0000";
							areaQuery.setAreaCode(areaCode);
							Area area = CacheUtils.get(areaCode);
							if(org.springframework.util.StringUtils.isEmpty(area)) {
								area = areaDao.get(areaQuery);
								CacheUtils.put(areaCode, area);
							}
							if(!org.springframework.util.StringUtils.isEmpty(area)) {
								XSSFCell  officeAreaCell = row.createCell(18);
								officeAreaCell.setCellValue(area.getTreeNames());
							}
							//所在市
							if(!org.springframework.util.StringUtils.isEmpty(company.getArea())) {
								XSSFCell  officeAreaCell = row.createCell(19);
								officeAreaCell.setCellValue(company.getArea().getTreeNames());
							}
						}
						
					}
				}
				
				
				//学位
				String highestDegree = CacheUtils.get("highest_degree", employee.getHighestDegree());
				if(org.springframework.util.StringUtils.isEmpty(highestDegree)) {
					highestDegree = DictUtils.getDictLabel("highest_degree", employee.getHighestDegree(), "");
					CacheUtils.put("highest_degree", employee.getHighestDegree(), highestDegree);
				}
				
				XSSFCell  highestDegreeCell = row.createCell(20);
				highestDegreeCell.setCellValue(highestDegree);
				
				//所教授的学生层次
				String studentLevel = CacheUtils.get("sys_student_level", employee.getStudentLevel());
				if(org.springframework.util.StringUtils.isEmpty(studentLevel)) {
					studentLevel = DictUtils.getDictLabel("sys_student_level", employee.getStudentLevel(), "");
					CacheUtils.put("sys_student_level", employee.getStudentLevel(), studentLevel);
				}
				
				XSSFCell  studentLevelCell = row.createCell(21);
				studentLevelCell.setCellValue(studentLevel);
				 
				//学习
				String learningSituation = CacheUtils.get("school_abroad", employee.getLearningSituation());
				if(org.springframework.util.StringUtils.isEmpty(learningSituation)) {
					learningSituation = DictUtils.getDictLabel("school_abroad", employee.getLearningSituation(), "");
					CacheUtils.put("school_abroad", employee.getLearningSituation(), learningSituation);
				}
				
				
				XSSFCell  learningSituationCell = row.createCell(22);
				learningSituationCell.setCellValue(learningSituation);
				XSSFCell  learningSituationExtCell = row.createCell(23);
				learningSituationExtCell.setCellValue(employee.getLearningSituationExt());
				
				//工作
				String workJob = CacheUtils.get("work_abroad", employee.getWorkJob());
				if(org.springframework.util.StringUtils.isEmpty(workJob)) {
					workJob = DictUtils.getDictLabel("work_abroad", employee.getWorkJob(), "");
					CacheUtils.put("work_abroad", employee.getWorkJob(), workJob);
				}
				
				XSSFCell  workJobCell = row.createCell(24);
				workJobCell.setCellValue(workJob);
				XSSFCell  workJobExtCell = row.createCell(25);
				workJobExtCell.setCellValue(employee.getWorkJobExt());
				
				//专业技术职务1
				EduTeacherTitle tt = employee.getEduTeacherTitle();
				if(!org.springframework.util.StringUtils.isEmpty(tt)) {
					EduTeacherTitle eduTeacherTitle = CacheUtils.get("ett",tt.getId());
					if(org.springframework.util.StringUtils.isEmpty(eduTeacherTitle)) {
						eduTeacherTitle = eduTeacherTitleDao.get(tt);
						if(!org.springframework.util.StringUtils.isEmpty(eduTeacherTitle)) {
						}
					}
					
					if(!org.springframework.util.StringUtils.isEmpty(eduTeacherTitle)) {
						CacheUtils.put("ett",tt.getId(),eduTeacherTitle);

						String[] eduTeacherTitles = eduTeacherTitle.getTreeNames().split("/");
						pullCell(row,26,eduTeacherTitles);
					}
				}
				
			
				EduProfessionalTitle pt = employee.getEduProfessionalTitle();
				if(!org.springframework.util.StringUtils.isEmpty(pt)) {
					EduProfessionalTitle eduProfessionalTitle = CacheUtils.get("ept",pt.getId());
					if(org.springframework.util.StringUtils.isEmpty(eduProfessionalTitle)) {
						eduProfessionalTitle = eduProfessionalTitleDao.get(pt);
					}
					
					if(!org.springframework.util.StringUtils.isEmpty(eduProfessionalTitle)) {
						CacheUtils.put("ept",pt.getId(),eduProfessionalTitle);
						String[] professionalTitles = eduProfessionalTitle.getTreeNames().split("/");
						pullCell(row,29,professionalTitles,eduProfessionalTitle.getRemarks());
					}
				}

				EduSubject es = employee.getSubjectResearch();
				if(!org.springframework.util.StringUtils.isEmpty(es)) {
					EduSubject eduSubject = CacheUtils.get("subject",es.getId());
					if(org.springframework.util.StringUtils.isEmpty(eduSubject)) {
						eduSubject = eduSubjectDao.get(es);
					}
					
					if(!org.springframework.util.StringUtils.isEmpty(eduSubject)) {
						CacheUtils.put("subject",es.getId(),eduSubject);
						String[] eduSubjects = eduSubject.getTreeNames().split("/");
						pullCell(row,32,eduSubjects);
					}
					
				}
				
				EduIndustry ei = employee.getEduIndustry();
				
				if(!org.springframework.util.StringUtils.isEmpty(ei)) {
					EduIndustry eduIndustry = CacheUtils.get("industry",ei.getId());
					if(org.springframework.util.StringUtils.isEmpty(eduIndustry)) {
						eduIndustry = eduIndustryDao.get(ei);
					}
					
					if(!org.springframework.util.StringUtils.isEmpty(eduIndustry)) {
						CacheUtils.put("industry",ei.getId(),eduIndustry);
						String[] eduIndustrys = eduIndustry.getTreeNames().split("/");
						pullCell(row,36,eduIndustrys);
					}
				}
				
				//是否博士后
				
				String yes = CacheUtils.get("sys_yes_no", employee.getIsPostdoctor());
				if(org.springframework.util.StringUtils.isEmpty(yes)) {
					yes = DictUtils.getDictLabel("sys_yes_no", employee.getIsPostdoctor(), "");
					CacheUtils.put("sys_yes_no", employee.getIsPostdoctor(), yes);
				}
				
				XSSFCell  yesCell = row.createCell(40);
				yesCell.setCellValue(yes);
				//担任导师
				
				String mentor = CacheUtils.get("sys_mentor", employee.getMentor());
				if(org.springframework.util.StringUtils.isEmpty(mentor)) {
					mentor = DictUtils.getDictLabel("sys_mentor", employee.getMentor(),  "");
					CacheUtils.put("sys_mentor", employee.getMentor(), mentor);
				}
				
				XSSFCell  mentorCell = row.createCell(41);
				mentorCell.setCellValue(mentor);
				//最高学历所学专业
				EduSubject em = employee.getSubjectMajor();
				if(!org.springframework.util.StringUtils.isEmpty(em)) {
					EduSubject subjectMajor = CacheUtils.get("subject",em.getId());
					if(org.springframework.util.StringUtils.isEmpty(subjectMajor)) {
						subjectMajor = eduSubjectDao.get(em);

					}
					if(!org.springframework.util.StringUtils.isEmpty(subjectMajor)) {
						CacheUtils.put("subject",em.getId(),subjectMajor);
						XSSFCell  subjectMajorCell = row.createCell(42);
						subjectMajorCell.setCellValue(subjectMajor.getDictLabel());
					}
				}

				//人才称号
				EduTalent eduTalentQuery = new EduTalent();
				eduTalentQuery.setEmployee(employee);
				List<EduTalent> eduTalents = eduTalentDao.findList(eduTalentQuery);
				
				
				StringBuilder sb = new StringBuilder();
				List<Integer> rets = new ArrayList<Integer>();
				int cnt = 0;
				for(EduTalent eduTalent :eduTalents) {
					
					String value = CacheUtils.get("sys_talent", eduTalent.getTalentCode());
					if(org.springframework.util.StringUtils.isEmpty(value)) {
						value = DictUtils.getDictLabel("sys_talent", eduTalent.getTalentCode(),  "");
						CacheUtils.put("sys_talent", eduTalent.getTalentCode(), value);
					}
					
					if(!org.springframework.util.StringUtils.isEmpty(eduTalent.getTalentCode())) {
						int code = Integer.valueOf(eduTalent.getTalentCode());
						rets.add(code);
					}
					sb.append(eduTalent.getTalentYear() + "   "  +value);
					sb.append("\n");
					cnt++;
				}
				int ret = 99;
				if(!CollectionUtils.isEmpty(rets)) {
					ret = Collections.min(rets);
				}
				String title = "基础人才";
				if (ret > 0 && ret < 22) {
					title = "高端人才";
				} else if (ret > 21 && ret < 37) {
					title = "突出人才";
				}
				
				if(org.springframework.util.StringUtils.isEmpty(title)) {
					if(employee.getIsPostdoctor().equals("1")) {
						title = "骨干人才";
					}
				}
				
				//入选年份人才称号
				XSSFCell  eduTalentCell = row.createCell(43);
				eduTalentCell.setCellValue(sb.toString());
				
				String schoolJob = CacheUtils.get("school_job", employee.getSchoolJob());
				if(org.springframework.util.StringUtils.isEmpty(schoolJob)) {
					schoolJob = DictUtils.getDictLabel("school_job", employee.getSchoolJob(),  "");
					CacheUtils.put("school_job", employee.getSchoolJob(), schoolJob);
				}
				
				XSSFCell  schoolJobCell = row.createCell(44);
				schoolJobCell.setCellValue(schoolJob);
				XSSFCell  schoolJobExtCell = row.createCell(45);
				schoolJobExtCell.setCellValue(employee.getSchoolJobExt());
				
				String industryJob = CacheUtils.get("industry_job", employee.getIndustryJob());
				if(org.springframework.util.StringUtils.isEmpty(industryJob)) {
					industryJob = DictUtils.getDictLabel("industry_job", employee.getIndustryJob(),  "");
					CacheUtils.put("industry_job", employee.getIndustryJob(), industryJob);
				}
				
				XSSFCell  industryJobCell = row.createCell(46);
				industryJobCell.setCellValue(industryJob);
				XSSFCell  industryJobExtCell = row.createCell(47);
				industryJobExtCell.setCellValue(employee.getIndustryJobExt());
				
				XSSFCell  titleCell = row.createCell(48);
				titleCell.setCellValue(title);
				
				XSSFCell  cntCell = row.createCell(49);
				cntCell.setCellValue(cnt);
				//46人才称号精选
				//47人才称号人数
				//47-66
                    
				
                sheet.addMergedRegion(new CellRangeAddress(j,j,50,68));
				
                XSSFCell  totalCell = row.createCell(50);
                totalCell.setCellValue(sb.toString());
				
				//职称类别+4 16 17 18 19
//				String postCode = employee.getPostCode();
//				if(!org.springframework.util.StringUtils.isEmpty(postCode)) {
//					XSSFCell  postCell = row.createCell(20);
//					postCell.setCellValue(yes);
//				}
//				
				
				i++;
				j++;
			}
		} catch (IOException e) {
			// TODO Auto-generated catch block
			//e.printStackTrace();
			logger.error("异常信息~userCode:{}",userCode);
		}finally {
			try {
				if(is!=null) {
					is.close();
				}
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return wb;
	}
	
	public void pullCell(XSSFRow row,int number,String[] ss) {
		for(int k=number;k<ss.length+number;k++) {
			XSSFCell iCell = row.createCell(k);
			iCell.setCellValue(ss[k-number]);
		}
	}
	
	public void pullCell(XSSFRow row,int number,String[] ss,String remarks) {
		for(int k=number;k<ss.length+number;k++) {
			XSSFCell iCell = row.createCell(k);
			iCell.setCellValue(ss[k-number] + " " + remarks);
		}
	}

}